package edu.colorado.karl.databases;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jfree.data.time.Minute;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;

/**
 * Database containing trend information from the house's sensors.
 */
public class LongTermDatabase {
	//	Used for log4j logging
	private static final Log log = LogFactory.getLog(LongTermDatabase.class);
	
	//	Note: See the Sensors database for an explanation of these TID values
	private static final int ZONE_TEMP = 0;
	private static final int CT_1 = 1;
	private static final int CT_2 = 2;
	private static final int CT_3 = 3;
	private static final int HOT_WATER = 4;
	private static final int HP_C_IN = 5;
	private static final int HP_C_OUT = 6;
	private static final int HP_DHW_IN = 7;
	private static final int HP_DHW_OUT = 8;
	private static final int HP_H_IN = 9;
	private static final int HP_H_OUT = 10;
	private static final int T_HT_1 = 11;
	private static final int T_HT_2 = 12;
	private static final int T_HT_3 = 14;
	private static final int T_OA = 13;
	private static final int T_PV_1 = 15;
	private static final int T_PV_2 = 16;
	private static final int T_PV_3 = 17;
	private static final int SP_R = 18;
	private static final int SP_S = 19;
	
	/**
	 * Returns the room and outside air temperatures.
	 * @return a list of temperatures.
	 */
	public ArrayList<SensorResponse> getAirTemperatures() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();
		
		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + ZONE_TEMP + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("RT", rs.getDouble(1)));
			}
			
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_OA + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("OAT", rs.getDouble(1)));
			}
			
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Returns the heat pump input and output water temperatures.
	 * @return a list of temperatures.
	 */
	public ArrayList<SensorResponse> getHeatPumpTemperatures() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();
		
		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			
			//	Cold Water Input
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + HP_C_IN + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("C IN", rs.getDouble(1)));
			}
			
			//	Cold Water Output
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + HP_C_OUT + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("C OUT", rs.getDouble(1)));
			}
			
			//	Hot Water Input
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + HP_H_IN + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("H IN", rs.getDouble(1)));
			}
			
			//	Hot Water Output
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + HP_H_OUT + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("H OUT", rs.getDouble(1)));
			}
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}

	/**
	 * Returns the hot water temperatures.
	 * @return a list of temperatures.
	 */
	public ArrayList<SensorResponse> getHotWaterTemperatures() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();
		
		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			
			//	Domestic Hot Water Temperature
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + HOT_WATER + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("DHW", rs.getDouble(1)));
			}
			
			//	Domestic Hot Water Input
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + HP_DHW_IN + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("DHW IN", rs.getDouble(1)));
			}
			
			//	Domestic Hot Water Output
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + HP_DHW_OUT + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("DHW OUT", rs.getDouble(1)));
			}
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Retrieves the solar panel temperatures from the database.  As the solar
	 * panels have multiple sensors, the average temperature for is computed 
	 * and returned.
	 * @return a list with a single temperature for the solar panels.
	 */
	public ArrayList<SensorResponse> getSolarPanelTemperature() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();
		// Panel temperature
		double solarTemp = 0.0;
		// Number of working panel sensors
		int sensorCount = 0;
		
		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			//	Get Solar Panels Temperature from the average of the 
			//	Solar Panels sensors
			Statement s = c.createStatement();
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					" where TID_ = " + T_PV_1 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				solarTemp += rs.getDouble(1);
				sensorCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_PV_2 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				solarTemp += rs.getDouble(1);
				sensorCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_PV_3 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				solarTemp += rs.getDouble(1);
				sensorCount++;
			}
			
			l.add(new SensorResponse("PVT", solarTemp / sensorCount));
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Retrieves the solar panel supply and return temperatures from the database.
	 * @return a list with the solar panel supply and return temperatures.
	 */
	public ArrayList<SensorResponse> getSolarPanelSupplyTemperatures() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();

		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					" where TID_ = " + SP_R + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("Return", rs.getDouble(1)));
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + SP_S + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				l.add(new SensorResponse("Supply", rs.getDouble(1)));
			}
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Retrieves the hot and cold tank temperatures from the database.  As both
	 * tanks have multiple sensors, the average temperature for each tank is 
	 * computed and returned.
	 * @return a list of sensor values.
	 */
	public ArrayList<SensorResponse> getTankTemperatures() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();
		// Tank temperatures
		double cTankTemp = 0.0, hTankTemp = 0.0;
		// Number of working tank sensors
		int cTankCount = 0, hTankCount = 0;
		
		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			//	Get Cold Tank Temperature from the average of the Cold Tank	sensors
			Statement s = c.createStatement();
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + CT_1 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				cTankTemp += rs.getDouble(1);
				cTankCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + CT_2 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				cTankTemp += rs.getDouble(1);
				cTankCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + CT_3 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				cTankTemp += rs.getDouble(1);
				cTankCount++;
			}
			
			l.add(new SensorResponse("CT", cTankTemp / cTankCount));
			
			//	Get Hot Tank Temperature from the average of the Hot Tank sensors
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_HT_1 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				hTankTemp += rs.getDouble(1);
				hTankCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_HT_2 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				hTankTemp += rs.getDouble(1);
				hTankCount++;
			}
			
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_HT_3 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				hTankTemp += rs.getDouble(1);
				hTankCount++;
			}
			
			l.add(new SensorResponse("HT", hTankTemp / hTankCount));
			
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Returns the values of the specified sensors for the specified time range.
	 * @param start			A date denoting the start time for the range
	 * @param end			A date denoting the end time for the range
	 * @param sensorNames	An array of sensor names indicating which sensors
	 * to use in the format: "TID#,SensorName"
	 * @return a collection of values containing the sensors' data during the
	 * specified range.
	 */
	public TimeSeriesCollection getValuesForTimeRange(Date start, Date end, 
													  String[] sensorNames) {
		Connection c = LongTermDatabaseConnector.getConnection();
		TimeSeriesCollection tsc = new TimeSeriesCollection();
		Timestamp tSStart = new Timestamp(start.getTime());
		Timestamp tSEnd = new Timestamp(end.getTime());
		
		//concat sensor TIDs into ORed list
		String sensors = "(";
		for(int j = 0; j < sensorNames.length; j++) {
			sensors += "TID_=" + sensorNames[j].substring(0, sensorNames[j].indexOf(","));
			if(j < sensorNames.length-1) {
				sensors += " OR ";
			}
		}
		sensors += ")";
		
		try{
			Statement s = c.createStatement();
			//Limit number of data points to be plotted at once to 2000pts
			s.executeQuery("SELECT COUNT(*) FROM TRENDDATA " +
					"WHERE " + sensors + //WHERE (TID_=0 OR TID_=2 OR ...)
					" AND DATE_STAMP_ > \"" + tSStart.toString() +
					"\" AND DATE_STAMP_ < \"" + tSEnd.toString() + "\"" +
					" AND RECORD_TYPE_=2");
			ResultSet rs = s.getResultSet();
			rs.first();
			int numPoints = rs.getInt(1);  //original # of points in selection
			int modulus = numPoints/2000+1; //at most plot 2000 points
			
			//get data for each selected sensor
			for(int i = 0; i < sensorNames.length; i++) {
				s.executeQuery ("SELECT DATE_STAMP_, DATA_VALUE_ FROM TRENDDATA " +
						"WHERE TID_ = " + sensorNames[i].substring(0, sensorNames[i].indexOf(","))
						+ " AND DATE_STAMP_ > \"" + tSStart.toString() +
						"\" AND DATE_STAMP_ < \"" + tSEnd.toString() + "\"" +
						" AND RECORD_TYPE_=2" +
						" AND SEQUENCENUMBER_ % " + Integer.toString(modulus) + " =0" +
						" group by DATE_STAMP_");
				rs = s.getResultSet();
				TimeSeries mySeries = new TimeSeries(sensorNames[i].substring(
						sensorNames[i].indexOf(",")+1) + " Data", Minute.class);
				Date d = new Date();
				//extract data from result set and put into a new series
				while(rs.next()) {
					d.setTime(rs.getTimestamp(1).getTime());
					mySeries.add(new Minute(d), rs.getDouble(2));
				}
				tsc.addSeries(mySeries);
				rs.close();
			}
			s.close();
		}
		catch (SQLException e) { 
			log.debug("SQL Exception:" + e.getMessage());
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		return tsc;
	}
}
